import java.sql.*;
import java.util.ArrayList;

public class Database {
	String db = LoginInfo.DB;
	String user = LoginInfo.USER;
	String pw = LoginInfo.PWD;
	Connection conn = null;
	Statement stmt = null;

	public boolean login(String u, String p) {
		// public static void main( String [] args){

		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		}
		;

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			/*
			 * String query = "INSERT INTO Person VALUES"+
			 * "('"+p.get(i).id+"','"+ //id p.get(i).fname+"','"+ //fname
			 * p.get(i).lname+"','"+ //lname p.get(i).dob+"','"+ // date of
			 * birth p.get(i).dod+"','"+ // date of death p.get(i).sex+"','"+ //
			 * gender p.get(i).addr+"','"+ // addr p.get(i).bio+"');";
			 */
			String query = "SELECT username, password FROM Users WHERE"
					+ " username = '" + u + "'";
			ResultSet outcome = stmt.executeQuery(query);
			outcome.first();
			String res = outcome.getString(2);
			System.out.println(res);
			conn.commit();
			if (p.equals(res)) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// System.out.println("Error " + e);
			return false;
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e1) {
			}
		}

	}
	
	public String permissions(String u){
		String perm = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT privs FROM Users WHERE username = '" + u + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			resultSet.first();
			perm = resultSet.getString("privs");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return perm;
	}

	public boolean reg(String u, String p) {
		// public static void main( String [] args){

		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		}
		;

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String query = "INSERT INTO Users VALUES" + "('" + u + "','" + p
					+ "','" + "r');";
			int outcome = stmt.executeUpdate(query);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// System.out.println("Error " + e);
			return false;
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e1) {
			}
		}

	}
	
	public ArrayList<Person> getAllPersons(){
		ArrayList<Person> result = new ArrayList<Person>();
		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		};

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String query = "SELECT pid, fname, lname FROM Person;";
			ResultSet outcome = stmt.executeQuery(query);
			conn.commit();
			outcome.first();
			while(!outcome.isAfterLast()){
				Person p = new Person();
				p.id = outcome.getString("pid");
				p.fname = outcome.getString("fname");
				p.lname = outcome.getString("lname");
				result.add(p);
				outcome.next();
			}
		} catch (SQLException e) {
			System.out.println("Error " + e);
		} finally {
			try {
				stmt.close();
				conn.close();
				return result;
			} catch (SQLException e1) {
				return result;
			}
		}
	}
	
	public ArrayList<User> getEdits(){
		ArrayList<User> result = new ArrayList<User>();
		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		};

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String query = "SELECT * FROM Edits;";
			ResultSet outcome = stmt.executeQuery(query);
			conn.commit();
			outcome.first();
			while(!outcome.isAfterLast()){
				User u = new User();
				u.username = outcome.getString("username");
				u.edits = outcome.getString("drev");
				u.personId = outcome.getString("personid");
				result.add(u);
				outcome.next();
			}
		} catch (SQLException e) {
			System.out.println("Error " + e);
		} finally {
			try {
				stmt.close();
				conn.close();
				return result;
			} catch (SQLException e1) {
				return result;
			}
		}
	}
	
	public Person getPerson(String id){
		Person result = new Person();
		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		};

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String query = "SELECT * FROM Person WHERE pid = '" + id + "';";
			ResultSet outcome = stmt.executeQuery(query);
			conn.commit();
			outcome.first();
			result.id = outcome.getString("pid");
			result.fname = outcome.getString("fname");
			result.lname = outcome.getString("lname");
			result.dob = outcome.getString("dob");
			result.dod = outcome.getString("dod");
			result.sex = outcome.getString("gender");
			result.addr = outcome.getString("addr");
			result.bio = outcome.getString("bio");
		} catch (SQLException e) {
			System.out.println("Error " + e);
		} finally {
			try {
				stmt.close();
				conn.close();
				return result;
			} catch (SQLException e1) {
				return result;
			}
		}
	}
	
	public ArrayList<User> getAllUsers(){
		ArrayList<User> result = new ArrayList<User>();
		try {
			// System.out.println("thought so");
			Class.forName(LoginInfo.DRIVER_CLASS);
			String dbURL = LoginInfo.DRIVER + db;
			System.out.println("...");
			conn = DriverManager.getConnection(dbURL, user, pw);
			System.out.println("................");

			// conn.close();
		} catch (SQLException e) {
			System.out.println("SQL Error " + e.getMessage());
		} catch (Exception e) {
			System.out.println("General Error " + e.getMessage());
		};

		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String query = "SELECT username, privs FROM Users;";
			ResultSet outcome = stmt.executeQuery(query);
			conn.commit();
			outcome.first();
			while(!outcome.isAfterLast()){
				User u = new User();
				u.username = outcome.getString("username");
				u.privs = outcome.getString("privs");
				result.add(u);
				outcome.next();
			}
		} catch (SQLException e) {
			System.out.println("Error " + e);
		} finally {
			try {
				stmt.close();
				conn.close();
				return result;
			} catch (SQLException e1) {
				return result;
			}
		}
	}
	
	public ArrayList<User> lookupUser(String name) {
		ArrayList<User> ulist = new ArrayList<User>();
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT username, privs FROM Users WHERE username LIKE '%" + name + "%';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				User u = new User();
				u.username = resultSet.getString("username");
				u.privs = resultSet.getString("privs");
				ulist.add(u);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return ulist;
	}
	
	public ArrayList<User> lookupEdits(String name) {
		ArrayList<User> ulist = new ArrayList<User>();
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT * FROM Edits WHERE username LIKE '%" + name + "%';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				User u = new User();
				u.username = resultSet.getString("username");
				u.edits = resultSet.getString("drev");
				u.personId = resultSet.getString("personid");
				ulist.add(u);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return ulist;
	}

	public ArrayList<Person> lookupName(String name) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT pid, fname, lname FROM Person WHERE fname LIKE '%" + name + "%' or lname LIKE '%" + name + "%';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.id = resultSet.getString("pid");
				p.fname = resultSet.getString("fname");
				p.lname = resultSet.getString("lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> lookupName(String first, String last) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT pid, fname, lname FROM Person WHERE fname LIKE '%" + first + "%' AND lname LIKE '%" + last + "%';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.id = resultSet.getString("pid");
				p.fname = resultSet.getString("fname");
				p.lname = resultSet.getString("lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getParents(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT p.pid, p.fname, p.lname FROM Person as p, ChildOf as c WHERE c.chid = " + id + " AND (c.par1 = p.pid OR c.par2 = p.pid);";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.id = resultSet.getString("p.pid");
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getParent(String id, String parent) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query;
			if(parent.equals("f")){
				query = "SELECT p.fname, p.lname FROM Person as p, ChildOf as c WHERE c.chid = " + id + " AND ((c.par1 = p.pid OR c.par2 = p.pid) AND p.gender = 'M');";
			}else if(parent.equals("m")){
				query = "SELECT p.fname, p.lname FROM Person as p, ChildOf as c WHERE c.chid = " + id + " AND ((c.par1 = p.pid OR c.par2 = p.pid) AND p.gender = 'F');";
			}else{
				query = "";
			}
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getChildren(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT DISTINCT fname, lname FROM Person, ChildOf WHERE (par1 = " + id + " OR par2 = " + id + " ) AND chid = pid;";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("fname");
				p.lname = resultSet.getString("lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getSiblings(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT DISTINCT fname, lname FROM Person, ChildOf as one, ChildOf as two WHERE one.chid = " + id + " AND two.par1 = one.par1 AND two.par2 = one.par2 AND two.chid = pid AND pid <> " + id + ";";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("fname");
				p.lname = resultSet.getString("lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getSibling(String id, String sib) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query;
			if(sib.equals("b")){
				query = "SELECT DISTINCT fname, lname FROM Person, ChildOf as one, ChildOf as two WHERE one.chid = " + id + " AND two.par1 = one.par1 AND two.par2 = one.par2 AND two.chid = pid AND pid <> " + id + " AND gender = 'M';";
			}else if(sib.equals("s")){
				query = "SELECT DISTINCT fname, lname FROM Person, ChildOf as one, ChildOf as two WHERE one.chid = " + id + " AND two.par1 = one.par1 AND two.par2 = one.par2 AND two.chid = pid AND pid <> " + id + " AND gender = 'F';";
			}else{
				query = "";
			}
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("fname");
				p.lname = resultSet.getString("lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getGrandparents(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT p.fname, p.lname FROM Person as p, ChildOf as c WHERE (c.par1 = p.pid OR c.par2 = p.pid) AND (c.chid = (SELECT p2.pid FROM Person as p2, ChildOf as c2 WHERE c2.chid = " + id + "  AND (c2.par1 = p2.pid)) OR c.chid = (SELECT p3.pid FROM Person as p3, ChildOf as c3 WHERE c3.chid = " + id + "  AND (c3.par2 = p3.pid)));";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getGrandchildren(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT p.fname, p.lname FROM Person as p, ChildOf as c WHERE c.chid = p.pid AND (c.par1 IN (SELECT DISTINCT pid FROM Person, ChildOf WHERE (par1 =  " + id + "   OR par2 =  " + id + "  ) AND chid = pid) OR c.par2 IN (SELECT DISTINCT pid FROM Person, ChildOf WHERE (par1 =  " + id + "   OR par2 =  " + id + "  ) AND chid = pid));";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return plist;
	}
	
	public ArrayList<Person> getSharedBDays(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT DISTINCT p.fname, p.lname FROM Person as p, Person as p2 WHERE LEFT(p.dob, 5) = LEFT(p2.dob, 5) AND p2.pid = '" + id + "' AND p.pid <> '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			return null;
		}
		return plist;
	}
	
	public ArrayList<Person> getDivorced(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT DISTINCT p.fname, p.lname FROM Person as p, Person as p2, Divorced WHERE ('" + id + "' = pid1 AND p.pid = pid2) OR (p.pid = pid1 AND '" + id + "' = pid2)";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			return null;
		}
		return plist;
	}
	
	public ArrayList<Person> getMarriedTo(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT DISTINCT p.fname, p.lname FROM Person as p, Person as p2, MarriedTo WHERE ('" + id + "' = pid1 AND p.pid = pid2) OR (p.pid = pid1 AND '" + id + "' = pid2)";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			return null;
		}
		return plist;
	}
	
	public ArrayList<Person> getAdoptedBy(String id) {
		ArrayList<Person> plist = null;
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT p.pid, p.fname, p.lname FROM Person as p, AdoptedBy as a WHERE a.chid = " + id + " AND (a.parid = p.pid OR a.parid2 = p.pid);";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();
			plist = new ArrayList<Person>();
			resultSet.first();
			while (!resultSet.isAfterLast()) {
				Person p = new Person();
				p.id = resultSet.getString("p.pid");
				p.fname = resultSet.getString("p.fname");
				p.lname = resultSet.getString("p.lname");
				plist.add(p);
				resultSet.next();
			}

		} catch (Exception e) {
			return null;
		}
		return plist;
	}
	
	public String getDOB(String id) {
		String date = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT dob FROM Person WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet outcome = pstmt.executeQuery();
			outcome.first();
			date = outcome.getString("dob");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return date;
	}
	
	public String getDOD(String id) {
		String date = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT dod FROM Person WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet outcome = pstmt.executeQuery();
			outcome.first();
			date = outcome.getString("dod");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return date;
	}
	
	public String getGender(String id) {
		String gen = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT gender FROM Person WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet outcome = pstmt.executeQuery();
			outcome.first();
			gen = outcome.getString("gender");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return gen;
	}
	
	public String getAddress(String id) {
		String addr = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT addr FROM Person WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet outcome = pstmt.executeQuery();
			outcome.first();
			addr = outcome.getString("addr");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return addr;
	}
	
	public String getBio(String id) {
		String bio = "";
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "SELECT bio FROM Person WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet outcome = pstmt.executeQuery();
			outcome.first();
			bio = outcome.getString("bio");
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
		return bio;
	}
	
	public boolean updateUser(String name, String privs) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");

			// the query that looks up partial names.
			String query = "UPDATE Users SET privs = '" + privs + "' WHERE username = '" + name + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int res = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updatePerson(Person p) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			String id = p.id;
			String fname = p.fname;
			String lname = p.lname;
			String dob = p.dob;
			String dod = p.dod;
			String gender = p.sex;
			String addr = p.addr;
			String bio = p.bio;
			// the query that looks up partial names.
			String query = "UPDATE Person SET fname = '" + fname + "', lname = '" + lname + "', dob = '" + dob + "', dod = '" + dod + "', gender = '" + gender + "', addr = '" + addr + "', bio = '" + bio + "' WHERE pid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int res = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean addPerson(Person p) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			String fname = p.fname;
			String lname = p.lname;
			String dob = p.dob;
			String dod = p.dod;
			String gender = p.sex;
			String addr = p.addr;
			String bio = p.bio;
			// the query that looks up partial names.
			String query = "INSERT INTO Person (fname, lname, dob, dod, gender, addr, bio) VALUES ('" + fname + "', '" + lname + "', '" + dob + "', '" + dod + "', '" + gender + "', '" + addr + "', '" + bio + "');";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int res = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateEdits(String user, String desc, String id) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "INSERT INTO Edits VALUES ('" + user + "', '" + desc + "', '" + id + "');";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int res = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateEdits(String user, String desc) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "INSERT INTO Edits (username, drev) VALUES ('" + user + "', '" + desc + "');";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int res = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateParents(String id, String par1, String par2) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM ChildOf WHERE chid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				query = "INSERT INTO ChildOf VALUES ('" + par1 + "', '" + par2 + "', '" + id + "');";
			}else{
				query = "UPDATE ChildOf SET par1 = '" + par1 + "', par2 = '" + par2 + "' WHERE chid = '" + id + "';";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateParents(String id, String par1) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM ChildOf WHERE chid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				query = "INSERT INTO (par1, chid)ChildOf VALUES ('" + par1 + "', '" + id + "');";
			}else{
				query = "UPDATE ChildOf SET par1 = '" + par1 + "' WHERE chid = '" + id + "';";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateAdoptedBy(String id, String par1, String par2) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM AdoptedBy WHERE chid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				query = "INSERT INTO AdoptedBy VALUES ('" + par1 + "', '" + par2 + "', '" + id + "');";
			}else{
				query = "UPDATE AdoptedBy SET parid = '" + par1 + "', parid2 = '" + par2 + "' WHERE chid = '" + id + "';";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateAdoptedBy(String id, String par1) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM AdoptedBy WHERE chid = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				query = "INSERT INTO AdoptedBy (parid, chid) VALUES ('" + par1 + "', '" + id + "');";
			}else{
				query = "UPDATE AdoptedBy SET parid = '" + par1 + "' WHERE chid = '" + id + "';";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateMarriedTo(String id, String par1) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM MarriedTo WHERE pid1 = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				System.out.println("Hey");
				query = "INSERT INTO MarriedTo (pid1, pid2) VALUES ('" + id + "', '" + par1 + "');";
			}else{
				query = "UPDATE MarriedTo SET pid2 = '" + par1 + "' WHERE pid1 = '" + id + "';";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean updateDivorced(String id, String par1) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			// the query that looks up partial names.
			String query = "SELECT * FROM Divorced WHERE pid1 = '" + id + "' AND pid2 = '" + par1 + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet res = pstmt.executeQuery();
			
			if(!res.next()){
				query = "INSERT INTO Divorced VALUES ('" + id + "', '" + par1 + "', 'NA', 'NA');";
			}
			pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
	
	public boolean deletePerson(String id) {
		String connectionURL = LoginInfo.DRIVER + LoginInfo.DB;
		Connection conn = null;
		try {
			Class.forName(LoginInfo.DRIVER_CLASS);

			System.out.println("Attemping connectoin to " + "connectionURL");
			System.out.println(".........");
			System.out.println("....");

			conn = DriverManager.getConnection(connectionURL, LoginInfo.USER,
					LoginInfo.PWD);
			conn.setAutoCommit(false);
			System.out.println("Connection Succesful");
			
			String query = "UPDATE ChildOf SET par1 = NULL WHERE par1 = '" + id + "';";
			PreparedStatement pstmt = conn.prepareStatement(query);
			int result = pstmt.executeUpdate();
			
			query = "UPDATE ChildOf SET par2 = NULL WHERE par2 = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			
			query = "DELETE FROM ChildOf WHERE chid = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			
			query = "DELETE FROM MarriedTo WHERE pid1 = '" + id + "' OR pid2 = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			
			query = "DELETE FROM Divorced WHERE pid1 = '" + id + "' OR pid2 = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			
			query = "DELETE FROM AdoptedBy WHERE chid = '" + id + "' OR parid = '" + id + "' OR parid2 = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			
			query = "DELETE FROM Edits WHERE personid = '" + id + "';";
			pstmt = conn.prepareStatement(query);
			result = pstmt.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println("Error " + e);
			return false;
		}
	}
}
